﻿using DTO;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAO
{
    public class SanVanDong_DAO
    {
        public static DataTable SelectAll()
        {
            string sql = "select * from SANVANDONG";
            return DataAccess.executeQuery(sql);
        }
        public static DataTable SelectTen()
        {
            string sql = "select MaSan,TenSan,DiaDiem,SucChua from SANVANDONG";
            return DataAccess.executeQuery(sql);
        }
        public static SanVanDong_DTO SelectByID(int id)
        {
            string sql = "select * from SANVANDONG where MaSan = " + id;
            DataTable dt = DataAccess.executeQuery(sql);
            SanVanDong_DTO dto = new SanVanDong_DTO();
            dto.MaSan = Convert.ToInt32(dt.Rows[0]["MaSan"]);
            dto.TenSan = Convert.ToString(dt.Rows[0]["TenSan"]);
            dto.DiaDiem = Convert.ToString(dt.Rows[0]["DiaDiem"]);
            dto.SucChua = Convert.ToInt32(dt.Rows[0]["SucChua"]);
            dto.NamXay = Convert.ToInt32(dt.Rows[0]["NamXay"]);

            dto.ByteTours = (byte[])dt.Rows[0]["Tour"];

            return dto;
        }
        public static DataTable SelectByTen(string ten)
        {
            string sql = "select * from SANVANDONG where TenSan = '" + ten + "'";
            DataTable dt = DataAccess.executeQuery(sql);
            return dt;
        }
        public static void Insert(SanVanDong_DTO dto)
        {
            string sql = "Insert INTO SANVANDONG (TenSan, DiaDiem, SucChua, NamXay, Tour)" +
                            "VALUES ('" + dto.TenSan + 
                                     "','" + dto.DiaDiem + 
                                     "'," + dto.SucChua + 
                                     "," + dto.NamXay + 
                                     ", @MyTour)";
            OleDbCommand command = new OleDbCommand(sql);
            OleDbParameter imageParameter = command.Parameters.AddWithValue("@MyTour", SqlDbType.Binary);
            imageParameter.Value = dto.ByteTours;
            imageParameter.Size = dto.ByteTours.Length;
            DataAccess.executeNonQuery(command);
        }
        public static void Update(SanVanDong_DTO dto)
        {
            string sql = "Update SANVANDONG set TenSan=('" + dto.TenSan +
                                            "'),DiaDiem=('" + dto.DiaDiem +  
                                            "'),SucChua=(" + dto.SucChua +
                                            "),NamXay=(" + dto.NamXay + 
                                            "), Tour = (@MyTour) where MaSan = " + dto.MaSan + "";
            OleDbCommand command = new OleDbCommand(sql);
            OleDbParameter imageParameter = command.Parameters.AddWithValue("@MyTour", SqlDbType.Binary);
            imageParameter.Value = dto.ByteTours;
            imageParameter.Size = dto.ByteTours.Length;
            DataAccess.executeNonQuery(command);
        }
        public static void Delete(int ma)
        {
            string sql = "Delete from SANVANDONG where MaSan = " + ma + "";
            DataAccess.executeNonQuery(sql);
        }
    }
}
